package com.flyu.dao.Provider;

import com.flyu.dao.BaseDao;
import com.flyu.pojo.Provider;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao {
    // 获取供应商列表
    public List<Provider> getProviderList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();

        if (connection != null){
            String sql = "select * from smbms_provider";
            Object[] params = {};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProCode(rs.getString("proCode"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return providerList;
    }
    // 根据条件获取供应商列表
    public List<Provider> getProviderListByRule(Connection connection, String providerCode, String providerName, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();

        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider where id is not null");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(providerCode)){
                sql.append(" and proCode like ?");
                list.add("%"+providerCode+"%");
            }
            if (!StringUtils.isNullOrEmpty(providerName)){
                sql.append(" and proName like ?");
                list.add("%"+providerName+"%");
            }
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize; // 下页起始位置
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, String.valueOf(sql), params);
            while (rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProCode(rs.getString("proCode"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProContact(rs.getString("proContact"));
                _provider.setProPhone(rs.getString("proPhone"));
                _provider.setProFax(rs.getString("proFax"));
                _provider.setCreationDate(rs.getTimestamp("creationDate"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return providerList;
    }
    // 统计符合条件的供应商数量
    public int getProviderCount(Connection connection, String providerCode, String providerName) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_provider where id is not null");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(providerCode)){
                sql.append(" and proCode like ?");
                list.add("%"+providerCode+"%");
            }
            if (!StringUtils.isNullOrEmpty(providerName)){
                sql.append(" and proName like ?");
                list.add("%"+providerName+"%");
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, String.valueOf(sql), params);
            if (rs.next()){
                count = rs.getInt("count");
            }
        }
        BaseDao.closeResource(null, pstm, rs);
        return count;

    }

    // 添加供应商
    public int addProvider(Connection connection, Provider provider) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;

        if (connection != null){
            String sql = "insert into smbms_provider (id, proCode, proName, " +
                    "proDesc, proContact, proPhone, proAddress, proFax, " +
                    "createdBy, creationDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            Object[] params = {provider.getId(), provider.getProCode(),provider.getProName(), provider.getProDesc(),
                    provider.getProContact(), provider.getProPhone(), provider.getProAddress(),
                    provider.getProFax(), provider.getCreatedBy(), provider.getCreationDate()};
            execute = BaseDao.execute(connection, pstm, sql, params);
        }
        BaseDao.closeResource(null, pstm, null);
        return execute;
    }
    // 删除供应商
    public int delProvider(Connection connection, int proId) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;
        if (connection != null){
            String sql = "delete from smbms_provider where id = ?";
            Object[] params = {proId};
            execute = BaseDao.execute(connection, pstm, sql, params);
        }
        BaseDao.closeResource(null, pstm, null);
        return execute;
    }
    // 根据id返回对应provider
    public Provider getProviderById(Connection connection, int proid) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Provider provider = null;
        if (connection != null) {
            String sql = "select * from smbms_provider where id = ?";
            Object[] params = {proid};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if (rs.next()) {
                provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setProDesc(rs.getString("proDesc"));
            }
        }
        BaseDao.closeResource(null, pstm, rs);
        return provider;
    }
    // 保存修改
    public int modifySave(Connection connection, Provider provider) throws SQLException {
        PreparedStatement pstm = null;
        int execute = 0;

        if (connection != null){
            String sql = "update smbms_provider set proName=?, proContact=?, proPhone=?, proFax=?, proAddress=?, proDesc=? where proCode=?";
            Object[] params = {provider.getProName(), provider.getProContact(), provider.getProPhone(), provider.getProFax(), provider.getProAddress(),
                    provider.getProDesc(), provider.getProCode()};
            execute = BaseDao.execute(connection, pstm, sql, params);
        }
        BaseDao.closeResource(null, pstm, null);
        return execute;
    }
}
